package cn.gson.crm.model.dao;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class Jdbc {

	private static Jdbc instance;
	private Connection conn;
	private PreparedStatement statement;
	private ResultSet resultSet;

	private Jdbc() {
		try {
			// 加载驱动
			Class.forName("com.mysql.jdbc.Driver");
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	public static Jdbc getInstace() {
		if (instance == null) {
			instance = new Jdbc();
		}

		return instance;
	}

	private void createConnection() {
		try {
			// 判断数据库连接如果是空或者已经关闭，就重写创建
			if (conn == null || conn.isClosed()) {
				conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/tr14", "root", "root");
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	/**
	 * 执行所有的增，删，改sql语句
	 * 
	 * @param sql
	 * @param objects
	 * @return
	 * @throws SQLException
	 */
	public boolean executeUpdate(String sql, Object... objects) throws SQLException {
		this.createStatement(sql, objects);

		// 执行sql
		boolean result = statement.executeUpdate() > 0;
		// 释放资源
		this.release();
		return result;
	}

	/**
	 * 只处理返回一天记录的sql
	 * 
	 * @param sql
	 * @param objects
	 * @throws SQLException
	 */
	public Map<String, Object> executeQuery(String sql, Object... objects) throws SQLException {
		this.createStatement(sql, objects);
		resultSet = statement.executeQuery();
		Map<String, Object> row = null;
		if (resultSet.next()) {
			row = new HashMap<>();
			ResultSetMetaData meta = resultSet.getMetaData();
			for (int i = 1; i <= meta.getColumnCount(); i++) {
				String lable = meta.getColumnName(i);
				row.put(lable, resultSet.getObject(lable));
			}
		}
		return row;
	}

	/**
	 * 返回多条记录
	 * 
	 * @param sql
	 * @param objects
	 * @return
	 * @throws SQLException
	 */
	public List<Map<String, Object>> executeQueryForList(String sql, Object... objects) throws SQLException {
		this.createStatement(sql, objects);
		resultSet = statement.executeQuery();
		List<Map<String, Object>> rows = new ArrayList<>();
		Map<String, Object> row;
		while (resultSet.next()) {
			row = new HashMap<>();
			ResultSetMetaData meta = resultSet.getMetaData();
			for (int i = 1; i <= meta.getColumnCount(); i++) {
				String lable = meta.getColumnName(i);
				row.put(lable, resultSet.getObject(lable));
			}
			rows.add(row);
		}
		return rows;
	}

	private void createStatement(String sql, Object... objects) throws SQLException {
		this.createConnection();
		statement = this.conn.prepareStatement(sql);
		// 统一的绑定参数
		if (objects != null && objects.length > 0) {
			for (int i = 1; i <= objects.length; i++) {
				statement.setObject(i, objects[i - 1]);
			}
		}
	}

	/**
	 * 释放资源
	 */
	private void release() {
		try {
			if (resultSet != null) {
				resultSet.close();
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		try {
			if (statement != null) {
				statement.close();
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		try {
			if (conn != null) {
				conn.close();
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	public static void main(String[] args) {
		Jdbc jdbc = Jdbc.getInstace();
		try {
			Map<String, Object> row = jdbc.executeQuery("select * from user");
			System.out.println(row.get("user_name"));
			List<Map<String, Object>> rows = jdbc.executeQueryForList("select * from user");
			for (Map<String, Object> map : rows) {
				System.out.println(map.get("user_name"));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

}
